﻿using System;
using System.IO;
using System.Reflection;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Configuration;

namespace DataModel
{
    public class DBTools : IDisposable
    {
        FriendsDBEntities dbFriends = null;
        string connString = String.Empty;

        #region Main methods

        public DBTools()
        {
            try
            {
                string dbPath = Path.GetDirectoryName(Assembly.GetAssembly(typeof(DBTools)).CodeBase);
                dbPath = Path.Combine(dbPath, @"..\App_Data\ASPNETDB.MDF");
                dbPath = dbPath.Replace(@"file:\", "");
                dbPath = Path.GetFullPath(dbPath);

                connString = @"metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;" + 
                             @"provider=System.Data.SqlClient;provider " +
                             @"connection string='Data Source=.\SQLEXPRESS;attachdbfilename=" + dbPath + ";" +
                             @"Integrated Security=True;" +
                             @"Connect Timeout=30;" +
                             @"User Instance=True;" +
                             @"Multipleactiveresultsets=True;" +
                             @"App=EntityFramework'";

                
                dbFriends = new FriendsDBEntities(connString);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        ~DBTools()
        {
            Dispose();
        }

        public void Dispose()
        {
            if (dbFriends != null)
            {
                dbFriends.Dispose();
            }
        }

        #endregion Main methods

        #region Events

        public List<FriendsEvent> GetAllEvents()
        {
            List<FriendsEvent> list = new List<FriendsEvent>();
            try
            {
                if (dbFriends != null)
                {
                    list = (from f in dbFriends.Events
                            join u in dbFriends.Users on f.Id_author equals u.UserId
                            where f.EventMark == "Active"
                            orderby f.Date
                            select new FriendsEvent
                            {
                                Id = f.Id,
                                EventDate = f.Date,
                                UserName = u.UserName,
                                Title = f.Title,
                                Note = f.Note,
                                EventMark = f.EventMark,
                                Id_parent = (f.Id_parent.HasValue ? f.Id_parent.Value : -1),
                                Id_UserParent = (f.Id_UserParent.HasValue ? f.Id_UserParent.Value : Guid.Empty),
                            }).ToList();
                }
            }
            catch(Exception ex)
            {
                list = null;
                throw new Exception(ex.Message, null);
            }
            return list;
        }

        public List<FriendsEvent> GetEventsForDay(DateTime day, string userName)
        {
            List<FriendsEvent> list = null;
            try
            {
                if (dbFriends != null)
                {
                    list = (from f in dbFriends.Events
                            join u in dbFriends.Users on f.Id_author equals u.UserId
                            where f.Date.Equals(day) &&
                            f.EventMark == "Active" &&
                            u.UserName.Equals(userName)
                            select new FriendsEvent()
                            {
                                Id = f.Id,
                                EventDate = f.Date,
                                UserName = u.UserName,
                                Title = f.Title,
                                Note = f.Note,
                                EventMark = f.EventMark,
                                Id_parent = (f.Id_parent.HasValue ? f.Id_parent.Value : -1),
                                Id_UserParent = (f.Id_UserParent.HasValue ? f.Id_UserParent.Value : Guid.Empty),
                            }).ToList();

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return list;
        }

        public List<FriendsEvent> GetEventsForUser(string userName)
        {
            return GetAllEvents().Where(u => u.UserName.Equals(userName)).ToList();
        }

        public void SaveChangesForEvent(decimal id, 
                                        DateTime eventDate, 
                                        string author, 
                                        string title, 
                                        string note)
        {
            Events friendsEvent = null;
            try
            {
                if (dbFriends != null)
                {
                    friendsEvent = dbFriends.Events.Where(f => f.Id == id).FirstOrDefault();
                    if (friendsEvent != null)
                    {
                        friendsEvent.Date = eventDate;
                        friendsEvent.Id_author = dbFriends.Users.Where(u => u.UserName == author).Select(s => s.UserId).FirstOrDefault();
                        friendsEvent.Title = title;
                        friendsEvent.Note = note;

                        dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        public FriendsEvent AddNewEvent(DateTime eventDate, string author, string title, string note)
        {
            FriendsEvent returnedFriendsEvent = null;
            int saveAmount = 0;
            try
            {
                Events friendsEvent = new Events();
                friendsEvent.Date = eventDate;
                friendsEvent.Title = title;
                friendsEvent.Note = note;
                friendsEvent.Id_author = 
                    dbFriends.Users.Where(u => u.UserName == author).Select(s => s.UserId).FirstOrDefault();
                friendsEvent.EventMark = "Active";

                dbFriends.Events.AddObject(friendsEvent);
                saveAmount = dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);

                if (saveAmount > 0)
                {
                    returnedFriendsEvent = new FriendsEvent();
                    returnedFriendsEvent.EventMark = "Active";
                    returnedFriendsEvent.Note = note;
                    returnedFriendsEvent.Title = title;
                    returnedFriendsEvent.UserName = author;
                    returnedFriendsEvent.EventDate = eventDate;
                    returnedFriendsEvent.Id = friendsEvent.Id;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return returnedFriendsEvent;
        }

        public FriendsEvent AddNewEventFromParent(decimal eventId, string userName)
        {
            FriendsEvent returnedFriendsEvent = null;
            int saveAmount = 0;
            try
            {
                returnedFriendsEvent = (from f in dbFriends.Events.AsEnumerable()
                                        where f.Id == eventId ||
                                        f.Id_parent == eventId
                                        select new FriendsEvent() 
                                        { 
                                            Id = f.Id,
                                            Id_parent = f.Id_parent.HasValue ? f.Id_parent.Value : 0,
                                            Id_UserParent = f.Id_UserParent.HasValue ? f.Id_UserParent.Value : Guid.Empty,
                                            EventDate = f.Date,
                                            EventMark = f.EventMark,
                                            Note = f.Note,
                                            Title = f.Title,
                                            Id_Author = f.Id_author,
                                            UserName = dbFriends.Users.Where(u => u.UserId == f.Id_author).FirstOrDefault().UserName,
                                        }
                                        ).FirstOrDefault();

                if (returnedFriendsEvent != null)
                    return returnedFriendsEvent;

                Guid userId = dbFriends.Users.Where(u => u.UserName == userName).FirstOrDefault().UserId;
                Events parentEvent = dbFriends.Events.Where(r => r.Id == eventId).FirstOrDefault();
                
                Events newEvent = new Events();
                newEvent.Id_author = userId; 
                newEvent.Date = parentEvent.Date;
                newEvent.EventMark = parentEvent.EventMark;
                newEvent.Note = parentEvent.Note;
                newEvent.Title = parentEvent.Title;
                newEvent.Id_parent = eventId;
                newEvent.Id_UserParent = parentEvent.Id_author;

                dbFriends.Events.AddObject(newEvent);
                saveAmount = dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);

                if (saveAmount > 0)
                {
                    returnedFriendsEvent = new FriendsEvent();
                    returnedFriendsEvent.EventMark = "Active";
                    returnedFriendsEvent.Note = newEvent.Note;
                    returnedFriendsEvent.Title = newEvent.Title;
                    returnedFriendsEvent.UserName = dbFriends.Users.Where(u => u.UserId == parentEvent.Id_author).FirstOrDefault().UserName; 
                    returnedFriendsEvent.EventDate = newEvent.Date;
                    returnedFriendsEvent.Id = newEvent.Id;
                    returnedFriendsEvent.Id_parent = eventId;
                    returnedFriendsEvent.Id_UserParent = parentEvent.Id_author;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return returnedFriendsEvent;
        }

        public decimal GetEventId(DateTime eventDate, string title, string author, string note)
        {
            decimal returnedId = 0;
            try
            {
                returnedId = (from f in dbFriends.Events
                              join u in dbFriends.Users on f.Id_author equals u.UserId
                              where f.Date == eventDate &&
                              f.Title == title &&
                              f.Note == note &&
                              u.UserName == author
                              select f.Id).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return returnedId;
        }

        public void DeleteEvent(decimal eventId)
        {
            try
            {
                Events friendsEvent = dbFriends.Events.Where(t => t.Id == eventId).FirstOrDefault();
                if (friendsEvent == null)
                    return;

                EventsArchive eventsArchive = new EventsArchive();
                eventsArchive.Date = friendsEvent.Date;
                eventsArchive.EventMark = "Deleted";
                eventsArchive.Id = friendsEvent.Id;
                eventsArchive.Id_author = friendsEvent.Id_author;
                eventsArchive.Id_parent = friendsEvent.Id_parent;
                eventsArchive.Id_UserParent = friendsEvent.Id_UserParent;
                eventsArchive.Note = friendsEvent.Note;
                eventsArchive.Title = friendsEvent.Title;
                
                dbFriends.AddToEventsArchive(eventsArchive);
                dbFriends.DeleteObject(friendsEvent);
                dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        public FriendsEvent GetEventFromId(decimal eventId)
        {
            FriendsEvent friendsEvent = null;
            try
            {
                Events events = dbFriends.Events.Where(t => t.Id == eventId).FirstOrDefault();
                if (events != null)
                {
                    friendsEvent = new FriendsEvent();
                    friendsEvent.Id = events.Id;
                    friendsEvent.EventDate = events.Date;
                    friendsEvent.Title = events.Title;
                    friendsEvent.Note = events.Note;
                    friendsEvent.UserName = dbFriends.Users.Where(f => f.UserId == events.Id_author).FirstOrDefault().UserName;
                    friendsEvent.Id_parent = events.Id_parent.HasValue ? events.Id_parent.Value : 0;
                    friendsEvent.Id_UserParent = events.Id_UserParent.HasValue ? events.Id_UserParent.Value : Guid.Empty;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return friendsEvent;
        }

        #endregion Events

        #region Users
        protected Guid GetUserIdFromName(string userName)
        {
            Guid userId = Guid.Empty;
            try
            {
                userId = (from u in dbFriends.Users
                          where u.UserName == userName
                          select u.UserId).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return userId;
        }

        public string GetUserNameFromEvent(FriendsEvent eventParam)
        {
            string userName = String.Empty;
            try
            {
                userName = (from u in dbFriends.Users
                            join f in dbFriends.Events on u.UserId equals f.Id_UserParent.Value
                            where f.Id_UserParent == eventParam.Id_UserParent
                            select u.UserName).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return userName;
        }

        /// <summary>
        /// Getting list of all user's Id except the one with a name by userName
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        protected List<Guid> GetOtherUsersIdList(string userName)
        {
            List<Guid> userIdList = new List<Guid>();
            try
            {
                var list = (from u in dbFriends.Users
                            where u.UserName != userName
                            select u.UserId).ToList();

                foreach(var item in list)
                {
                    userIdList.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return userIdList;
        }

        internal string GetUserNameById(Guid userId)
        {
            string userName = String.Empty;
            try
            {
                userName = (from u in dbFriends.Users
                            where u.UserId == userId
                            select u.UserName).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return userName;
        }

        #endregion Users

        #region Messages

        public List<UserMessages> GetMessagesForUser(string userName)
        {
            List<UserMessages> messagesList = new List<UserMessages>();
            try
            {
                Guid userId = dbFriends.Users.Where(u => u.UserName == userName).FirstOrDefault().UserId;
                var list = (from m in dbFriends.Messages
                            where m.Id_Receiver == userId
                            select m).ToList();

                foreach (var item in list)
                {
                    UserMessages msg = new UserMessages();
                    Guid senderId = dbFriends.Events.Where(f => f.Id == item.Id_Event).FirstOrDefault().Id_author;

                    msg.Id = item.Id;
                    msg.IdEvent = item.Id_Event;
                    msg.EventTitle = dbFriends.Events.Where(f => f.Id == item.Id_Event).FirstOrDefault().Title;
                    msg.Receiver = userName;
                    msg.Sender = dbFriends.Users.Where(u => u.UserId == senderId).FirstOrDefault().UserName;
                    msg.EventMark = item.EventMark;
                    msg.EventDate = dbFriends.Events.Where(f => f.Id == item.Id_Event).FirstOrDefault().Date;
                    msg.WasRead = Boolean.Parse(item.WasRead);
                    messagesList.Add(msg);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return messagesList;
        }

        public void SendMessageToOtherUsers(decimal eventId, string senderName)
        {
            Guid senderId = GetUserIdFromName(senderName);
            try
            {
                foreach (Guid item in GetOtherUsersIdList(senderName))
                {
                    Messages message = new Messages();
                    message.Id_Event = eventId;
                    message.Id_Sender = senderId;
                    message.Id_Receiver = item;
                    message.EventMark = "New event";
                    message.WasRead = "false";
                    dbFriends.AddToMessages(message);
                    dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        public void MarkMessageFromFeedback(int selectedMsgId, bool isAccepted)
        {
            try
            {
                Messages message = dbFriends.Messages.Where(m => m.Id == selectedMsgId).FirstOrDefault();
                if (message != null)
                {
                    message.EventMark = isAccepted == true ? "Accepted" : "Rejected";
                    dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        public void DeleteMessage(decimal msgId)
        {
            try
            {
                Messages message = dbFriends.Messages.Where(m => m.Id == msgId).FirstOrDefault();
                if (message != null)
                {
                    dbFriends.DeleteObject(message);
                    dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        public void MarkMessageAsRead(int msgId)
        {
            try
            {
                Messages message = dbFriends.Messages.Where(m => m.Id == msgId).FirstOrDefault();
                if (message != null)
                {
                    message.WasRead = "true";
                    dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        #endregion Messages

        #region Feedback

        public void SendFeedback(decimal eventId, string userName, bool isAccepted, string note)
        {
            EventsFeedback feedback = null;
            try
            {
                if (dbFriends.EventsFeedback.Where(t => t.Id_Event == eventId).Count() == 0)
                {
                    feedback = new EventsFeedback();
                    feedback.Id_Event = eventId;
                    feedback.Id_User = dbFriends.Users.Where(u => u.UserName == userName).FirstOrDefault().UserId;
                    feedback.Mark = isAccepted.ToString();
                    feedback.Note = note;
                    dbFriends.AddToEventsFeedback(feedback);
                }
                else
                {
                    feedback = dbFriends.EventsFeedback.Where(t => t.Id_Event == eventId).FirstOrDefault();
                    feedback.Mark = isAccepted.ToString();
                    feedback.Note = note;
                }
                dbFriends.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
        }

        public List<UserEventsFeedback> GetFeedbackForEvent(decimal eventId)
        {
            List<UserEventsFeedback> feedbackList = new List<UserEventsFeedback>();

            try
            {
                var items = (from f in dbFriends.EventsFeedback.AsEnumerable()
                             where f.Id_Event == eventId
                             select f).ToList();

                foreach (var item in items)
                {
                    UserEventsFeedback userFeedback = new UserEventsFeedback();
                    userFeedback.Id_Event = item.Id_Event;
                    userFeedback.Id_User = item.Id_User;
                    userFeedback.Mark = item.Mark;
                    userFeedback.Note = item.Note;
                    feedbackList.Add(userFeedback);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, null);
            }
            return feedbackList;
        }

        #endregion Feedback              
        
    }
}
